## 21.4. Mysql.pm API

The Mysql.pm API is identical to the Msql API (with "Msql" replaced with "Mysql" in all places) except for the following differences.

Mysql::connect |

    $db = Mysql->connect($host, $database, $user, $password);

In addition to the three connect methods that are identical to `Msql::connect`, `Mysql::connect` provides a fourth method that requires an additional password argument. The first argument is the hostname or IP address of the MySQL server. If `undef` is passed as this argument, the module connects to the Unix-style socket on the localhost. The second argument is the name of the initial database to select. This can always be changed later with `Mysql::selectdb`. You may also supply `undef` as the second argument to select no initial database. The third argument is the username of the user connecting to the database. To successfully connect, the username must exist in the MySQL access tables. The final argument is the password of the user.

#### Example

>     use Mysql;

>     $db = Mysql->connect(undef,'mydata','webuser','super_secret_squirrel');
>     # The database handle is now connected to the local MySQL server using the
>     # database 'mydata'. The user name 'webuser' was used to connect who had
>     # the password 'super_secret_squirrel'.

Mysql::errno |

    $error_number = $db->errno;

`Mysql::errno` returns the error number of the last error. This error number corresponds to the error message returned from `Msql::errmsg`.

#### Example

>     use Mysql;
>     my $db = Mysql->connect(undef,'mydata','webuser','super_secret_squirrel');

>     # There is a parse error in this query...
>     my $output = $db->query('SELECT * from mydata');

>     if (not $output) {
>     				print "Error " . $output->errno . ": " . $output->errmsg . "\n";
>     }

Mysql::FIELD_TYPE_* |

    In addition to the Mysql::TYPE_* datatype
    functions that are identical to the Msql::TYPE_*
    functions, Mysql.pm provides these
    extra datatype functions:

> `&Mysql::FIELD_TYPE_BLOB`

> `&Mysql::FIELD_TYPE_CHAR`

> `&Mysql::FIELD_TYPE_DECIMAL`

> `&Mysql::FIELD_TYPE_DATE`

> `&Mysql::FIELD_TYPE_DATETIME`

> `&Mysql::FIELD_TYPE_DOUBLE`

> `&Mysql::FIELD_TYPE_FLOAT`

> `&Mysql::FIELD_TYPE_INT24`

> `&Mysql::FIELD_TYPE_LONGLONG`

> `&Mysql::FIELD_TYPE_LONG_BLOB`

> `&Mysql::FIELD_TYPE_LONG`

> `&Mysql::FIELD_TYPE_MEDIUM_BLOB`

> `&Mysql::FIELD_TYPE_NULL`

> `&Mysql::FIELD_TYPE_SHORT`

> `&Mysql::FIELD_TYPE_STRING`

> `&Mysql::FIELD_TYPE_TIME`

> `&Mysql::FIELD_TYPE_TIMESTAMP`

> `&Mysql::FIELD_TYPE_TINY_BLOB`

> `&Mysql::FIELD_TYPE_VAR_STRING`

#### Example

>     use Mysql;
>     my $db = Mysql->connect(undef,'mydata');
>     my $output = $db->query("SELECT name, data from myothertable");

>     if ($output->type->[0] = &Mysql::FIELD_TYPE_STRING) {
>     				print "Name is a STRING.\n";
>     }

Mysql::Statement::affectedrows |

    $number_of_affected_rows  = $statement_handle->affectedrows;

`Msql::Statement::affectedrows` returns the number of rows that were affected by the query. This function is useful since Mysql.pm returns a statement handle even on non-`SELECT` statements.

#### Example

>     use Mysql;
>     my $db = Mysql->connect(undef,'mydata');

>     my $output = $db->query("UPDATE mytable set name='bob' where name='joe'");
>     print $output->affectedrows . " rows were updated.\n";

Mysql::Statement::info |

    $info  = $sth->info;

`Mysql::Statement::info` returns extra results from certain queries that do not have specialized functions in Mysql.pm, such as `ALTER TABLE` and `LOAD DATA INFILE`. For example, when using `LOAD DATA INFILE`, `Mysql::Statement::info` returns the number of records inserted, the number deleted, the number skipped and the number of unparsable entries.

#### Example

>     use Mysql;
>     $db = Mysql->connect(undef,'mydata');

>     my $output = $db->query("LOAD DATA INFILE 'mydata.dat' INTO TABLE mytable");

>     my $info = $output->info($output);
>     print "LOAD DATA result: $info\n";

Mysql::Statement::insertid |

    $new_id  = $statement_handle->insertid;

`Mysql::Statement::insertid` returns the current value of the `auto_increment` field (if there is one) in the table. If there is no `auto_increment` field in the table, the function returns an undefined value `undef`.

#### Example

>     use Mysql;
>     my $db = Mysql->connect(undef,'mydata');

>     my $output = $db->query(
>        "INSERT into mytable (id, name, date) VALUES ('','bob','today')";

>     my $new_id = $output->insertid;
>     print "Bob was entered with an ID number of $new_id.\n";

Mysql::Statement::isblob |

    @blobs  = $statement_handle->isblob;

`Mysql::Statement::isblob` returns a list of boolean values indicating if the fields contained in the statement handle are of a `BLOB` type. If called in a scalar context, the function returns a reference to an array.

#### Example

>     use Mysql;
>     $db = Mysql->connect(undef,'mydata');

>     my $output = $db->query('SELECT name, data from myothertable');

>     if ($output->isblob->[0]) {
>     				print "Name is a BLOB.\n";
>     } else {
>     				print "Name is not a BLOB.\n";
>     }

Mysql::query |

Unlike Msql.pm, Mysql.pm returns a statement handle even with `non-SELECT` queries, such as `INSERT`, `UPDATE`, and `DELETE`.

#### Example

>     use Mysql;
>     my $db = Mysql->connect(undef,'mydata');

>     my $output = $db->query("UPDATE mytable set name='joe' where name='bob'");
>     # $output is a statement handle.

Copyright © 2001 O'Reilly & Associates. All rights reserved.